﻿using System;
using System.Data;
using System.Web;
using System.Data.SqlClient;

namespace sdamarketing.App_Code
{
    public class Users
    {
        // dodavanje na nov korisnik
        public static void AddUser(string firstName, string lastName, string username, string password, string email, string question, string answer, int privilege,int Sex, int Education, int Social, int Age, int Nationality,int Region,int Locality,int income,string slikaURL)
        {
            DateTime dateCreated = DateTime.Now;

            SqlConnection connection = ConnectionMenager.GetConnection();

            SqlParameter firstNameParam = new SqlParameter("@firstNameParam", firstName);
            SqlParameter lastNameParam = new SqlParameter("@lastNameParam", lastName);
            SqlParameter usernameParam = new SqlParameter("@usernameParam", username);
            SqlParameter passwordParam = new SqlParameter("@passwordParam", password);
            SqlParameter emailParam = new SqlParameter("@emailParam", email);
            SqlParameter questionParam = new SqlParameter("@questionParam", question);
            SqlParameter answerParam = new SqlParameter("@answerParam", answer);
            SqlParameter privilegeParam = new SqlParameter("@privilegeParam", privilege);
            SqlParameter idSexParam = new SqlParameter("@idSexParam", Sex);
            SqlParameter idAgeParam = new SqlParameter("@idAgeParam", Age);
            SqlParameter socialParam = new SqlParameter("@socialParam", Social);
            SqlParameter educationParam = new SqlParameter("@educationParam", Education);
            SqlParameter NationalityParam = new SqlParameter("@NationalityParam", Nationality);
            SqlParameter RegionParam = new SqlParameter("@RegionParam",Region);
            SqlParameter LocalityParam = new SqlParameter("@LocalityParam", Locality);
            SqlParameter dateCreatedParam = new SqlParameter("@dateCreatedParam", dateCreated);
            SqlParameter slikaParam = new SqlParameter("@slikaParam", slikaURL);
            SqlParameter incomeParam = new SqlParameter("@incomeParam", income);

            string sql = @"INSERT INTO Users (firstName, lastName, username, password, eMail, securityQuestion, securityAnswer, dateCreated, idRole,idSex,idEducation,idSocialStructure,idAge,idNationality,idRegion,idLocality,idIncome,slika) VALUES (@firstNameParam, @lastNameParam, @usernameParam, @passwordParam, @emailParam, @questionParam, @answerParam, @dateCreatedParam, @privilegeParam,@idSexParam,@educationParam,@socialParam,@idAgeParam,@NationalityParam,@RegionParam,@LocalityParam,@incomeParam,@slikaParam)";
            SqlCommand command = new SqlCommand(sql, connection);

            command.Parameters.Add(firstNameParam);
            command.Parameters.Add(lastNameParam);
            command.Parameters.Add(usernameParam);
            command.Parameters.Add(passwordParam);
            command.Parameters.Add(emailParam);
            command.Parameters.Add(dateCreatedParam);
            command.Parameters.Add(questionParam);
            command.Parameters.Add(answerParam);
            command.Parameters.Add(privilegeParam);
            command.Parameters.Add(idSexParam);
            command.Parameters.Add(idAgeParam);
            command.Parameters.Add(socialParam);            
            command.Parameters.Add(educationParam);
            command.Parameters.Add(NationalityParam);
            command.Parameters.Add(RegionParam);
            command.Parameters.Add(LocalityParam);
            command.Parameters.Add(slikaParam);
            command.Parameters.Add(incomeParam);

            command.ExecuteNonQuery();
            connection.Close();
        }

        public static int FindUser(string username)
        {
            SqlConnection connection = ConnectionMenager.GetConnection();
            string sql = "SELECT idUser FROM Users WHERE username = " + "'" + username.ToString() + "' AND isDeleted='0'";

            SqlCommand command = new SqlCommand(sql, connection);
            command.CommandType = CommandType.Text;

            SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult);

            int IDUser = 0;
            reader.Read();
            try
            {
                //IDUser = reader.GetInt32(0);
                IDUser = int.Parse(reader[0].ToString());
            }
            catch (Exception)
            {
                IDUser = 0;
            }
            reader.Close();
            connection.Close();
            return IDUser;
        }


        // Proveruva dali ima user so toj username i pass vo baza. Vraka dali e blokiran i go vraka Id-to na korisnikot
        public static DataSet authenticateUser(string username, string password)
        {
            DataSet dataSet = new DataSet();
            string sql = "SELECT idUser, isBlock, isDeleted, idRole FROM Users WHERE username = '" + username.ToString() + "' AND password = '" + password.ToString() + "' AND isDeleted='0'";

            SqlConnection connection = ConnectionMenager.GetConnection();
            SqlCommand command = new SqlCommand(sql, connection);
            command.CommandType = CommandType.Text;

            SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
            dataAdapter.Fill(dataSet, "User");

            connection.Close();
            return dataSet;
        }

        // Prebaruvaj spored nekoj vnesenite opcii
        public static DataSet Search(string firstName, string lastName, string username, string privilege)
        {
            DataSet dataSet = new DataSet();
            string sql = "SELECT idUser, firstName, lastName, username, name, isBlock  FROM Users INNER JOIN Roles ON Roles.idRole = Users.idRole WHERE firstName like '%" + firstName.ToString() + "%'AND lastName like '%" + lastName.ToString() + "%' AND username like '%" + username.ToString() + "%' AND Users.idRole like '%" + privilege.ToString() + "%' AND isDeleted = '0'";

            SqlConnection connection = ConnectionMenager.GetConnection();
            SqlCommand command = new SqlCommand(sql, connection);
            command.CommandType = CommandType.Text;

            SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
            dataAdapter.Fill(dataSet, "DataSet");

            connection.Close();
            
            return dataSet;
        }
        public static DataSet SearchExcludeUser(int userID,string firstName, string lastName, string username, string privilege)
        {
            DataSet dataSet = new DataSet();
            string sql = "SELECT idUser, firstName, lastName, username, name, isBlock  FROM Users INNER JOIN Roles ON Roles.idRole = Users.idRole WHERE idUser!='"+userID+"' AND firstName like '%" + firstName.ToString() + "%'AND lastName like '%" + lastName.ToString() + "%' AND username like '%" + username.ToString() + "%' AND Users.idRole like '%" + privilege.ToString() + "%' AND isDeleted = '0'";

            SqlConnection connection = ConnectionMenager.GetConnection();
            SqlCommand command = new SqlCommand(sql, connection);
            command.CommandType = CommandType.Text;

            SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
            dataAdapter.Fill(dataSet, "DataSet");

            connection.Close();

            return dataSet;
        }
        // Editiranje na korisnikot od strana na Administratorot
        public static void EditUser(string username, string firstName, string lastName, int privilege, string blocked)
        {
            DateTime dateModify = DateTime.Now;

            SqlConnection connection = ConnectionMenager.GetConnection();
            SqlParameter firstNameParam = new SqlParameter("@firstNameParam", firstName);
            SqlParameter lastNameParam = new SqlParameter("@lastNameParam", lastName);
            SqlParameter privilegeParam = new SqlParameter("@privilegeParam", privilege);
            SqlParameter blockedParam = new SqlParameter("@blockedParam", blocked);

            SqlParameter dateModifyParam = new SqlParameter("@dateModifyParam", dateModify);

            string sql = @"UPDATE Users SET firstName = @firstNameParam, lastName = @lastNameParam, dateModify = @dateModifyParam, idRole = @privilegeParam, isBlock = @blockedParam WHERE username='" + username + "'";
            SqlCommand command = new SqlCommand(sql, connection);

            command.Parameters.Add(firstNameParam);
            command.Parameters.Add(lastNameParam);
            command.Parameters.Add(dateModifyParam);
            command.Parameters.Add(privilegeParam);
            command.Parameters.Add(blockedParam);


            command.ExecuteNonQuery();
            connection.Close();
        }

        // Editiranje na korisnikot od strana na samiot korisnik
        public static void SaveChangesForUser(int idUser, string firstName, string lastName, string username, string password, string email, string question, string answer,int sex,int edu,int reg,int age,int ss,int nat,int inc,int loc,string slikaURL)
        {
            DateTime dateModify = DateTime.Now;

            SqlConnection connection = ConnectionMenager.GetConnection();

            SqlParameter firstNameParam = new SqlParameter("@firstNameParam", firstName);
            SqlParameter lastNameParam = new SqlParameter("@lastNameParam", lastName);
            SqlParameter usernameParam = new SqlParameter("@usernameParam", username);
            SqlParameter passwordParam = new SqlParameter("@passwordParam", password);
            SqlParameter emailParam = new SqlParameter("@emailParam", email);
            SqlParameter questionParam = new SqlParameter("@questionParam", question);
            SqlParameter answerParam = new SqlParameter("@answerParam", answer);

            SqlParameter sexParam = new SqlParameter("@sexParam", sex);
            SqlParameter eduParam = new SqlParameter("@eduParam", edu);
            SqlParameter regParam = new SqlParameter("@regParam", reg);
            SqlParameter ageParam = new SqlParameter("@ageParam", age);
            SqlParameter ssParam = new SqlParameter("@ssParam", ss);
            SqlParameter natParam = new SqlParameter("@natParam", nat);
            SqlParameter incParam = new SqlParameter("@incParam", inc);
            SqlParameter locParam = new SqlParameter("@locParam", loc);
            SqlParameter slikaParam = new SqlParameter("@slikaParam", slikaURL);

            SqlParameter dateModifyParam = new SqlParameter("@dateModifyParam", dateModify);
            
            string sql = @"UPDATE Users SET idSex=@sexParam, idEducation=@eduParam, idRegion=@regParam, idAge=@ageParam, idSocialStructure=@ssParam, idNationality=@natParam,idIncome=@incParam,idLocality=@locParam, firstName = @firstNameParam, lastName = @lastNameParam, username = @usernameParam, password = @passwordParam, dateModify = @dateModifyParam, securityQuestion = @questionParam, securityAnswer = @answerParam,slika=@slikaParam WHERE idUser='" + idUser.ToString() + "'";
            SqlCommand command = new SqlCommand(sql, connection);

            command.Parameters.Add(firstNameParam);
            command.Parameters.Add(lastNameParam);
            command.Parameters.Add(usernameParam);
            command.Parameters.Add(passwordParam);
            command.Parameters.Add(emailParam);
            command.Parameters.Add(dateModifyParam);
            command.Parameters.Add(questionParam);
            command.Parameters.Add(answerParam);

            command.Parameters.Add(sexParam);
            command.Parameters.Add(eduParam);
            command.Parameters.Add(regParam);
            command.Parameters.Add(ageParam);
            command.Parameters.Add(ssParam);
            command.Parameters.Add(natParam);
            command.Parameters.Add(incParam);
            command.Parameters.Add(locParam);
            command.Parameters.Add(slikaParam);

            command.ExecuteNonQuery();
            connection.Close();
        }

        public static void isDeleteUser(string username) // go stavam is deleted na 1
        {
            DateTime dateModify = DateTime.Now;
            DateTime dateDeleted = DateTime.Now;
            int del = 1;

            SqlConnection connection = ConnectionMenager.GetConnection();

            SqlParameter dateDeletedParam = new SqlParameter("@dateDeletedParam", dateDeleted);
            SqlParameter dateModifyParam = new SqlParameter("@dateModifyParam", dateModify);
            SqlParameter delParam = new SqlParameter("@delParam", del);


            string sql = @"UPDATE Users SET dateModify = @dateModifyParam, dateDeleted = @dateDeletedParam, isDeleted=@delParam WHERE username='" + username + "'";
            SqlCommand command = new SqlCommand(sql, connection);

            command.Parameters.Add(delParam);
            command.Parameters.Add(dateModifyParam);
            command.Parameters.Add(dateDeletedParam);

            command.ExecuteNonQuery();
            connection.Close();
        }
        public static void AddPrijatel(int tipkomanda,int user,int prijatel,string username,string usernamePrijatel)
        {
            SqlConnection connection = ConnectionMenager.GetConnection();
           
            SqlParameter prijatelParam = new SqlParameter("@prijatelParam", prijatel);
            SqlParameter userParam = new SqlParameter("@userParam", user);
            SqlParameter usernameParam = new SqlParameter("@usernameParam", username);
            SqlParameter usernamePrijatelParam = new SqlParameter("@usernamePrijatelParam", usernamePrijatel);
            if (tipkomanda == 0)
            {
                string sql = @"INSERT INTO Prijatel (idUser,idUserPrijatel,isPrijatel,Username,UsernamePrijatel) VALUES (@userParam,@prijatelParam,1,@usernameParam,@usernamePrijatelParam)";
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.Add(prijatelParam);
                command.Parameters.Add(userParam);
                command.Parameters.Add(usernameParam);
                command.Parameters.Add(usernamePrijatelParam);
                command.ExecuteNonQuery();
                connection.Close();
            }
            else
            {
                string sql = @"UPDATE SET Prijatel idUser=@userParam,idUserPrijatel=@prijatelParam,isPrijatel=1 Where Username=@usernameParam";
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.Add(prijatelParam);
                command.Parameters.Add(userParam);
                command.Parameters.Add(usernameParam);
                command.Parameters.Add(usernamePrijatelParam);
                command.ExecuteNonQuery();
                connection.Close();
            }  
        }
        public static void RemovePrijatel(int idUser,string Prijatel)
        { 
            SqlConnection connection = ConnectionMenager.GetConnection();
            string sql = @"DELETE FROM Prijatel WHERE idUser='"+idUser+"' AND UsernamePrijatel='"+Prijatel+"'";
            SqlCommand command = new SqlCommand(sql, connection);
            command.ExecuteNonQuery();
            connection.Close();
        }

        public DataSet getUserData(String username)
        {
            DataSet dataSet = new DataSet();
            int id = FindUser(username);
            //if (id != 0)
            //{
            String sql = "SELECT Age.fromAndTo, Educations.education, Nationality.Nacionalnost, Localitys.locality, Sex.sex, Regions.region, Users.idUser, Users.firstName, Users.lastName, Users.eMail, Users.slika FROM Users, Age, Educations, Sex, Localitys, Regions, Nationality WHERE Age.idAge = Users.idAge AND Users.idEducation = Educations.idEducation AND  Users.idLocality = Localitys.idLocality AND  Users.idNationality = Nationality.idNacionalnost AND Users.idRegion = Regions.idRegion AND Users.idSex = Sex.idSex AND Users.username='" + username + "'";

            SqlConnection connection = ConnectionMenager.GetConnection();
            SqlCommand command = new SqlCommand(sql, connection);
            command.CommandType = CommandType.Text;

            SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
            dataAdapter.Fill(dataSet, "Data");

            connection.Close();
            return dataSet;
        }
        public DataSet getUserData(int id)
        {
            DataSet dataSet = new DataSet();
            if (id != 0)
            {
                String sql = "SELECT Age.fromAndTo, Educations.education, Nationality.Nacionalnost, Localitys.locality, Sex.sex, Regions.region, Users.idUser, Users.firstName, Users.lastName, Users.eMail, Users.slika FROM Age INNER JOIN Users ON Age.idAge = Users.idAge INNER JOIN Educations ON Users.idEducation = Educations.idEducation INNER JOIN Localitys ON Users.idLocality = Localitys.idLocality INNER JOIN Nationality ON Users.idNationality = Nationality.idNacionalnost INNER JOIN Regions ON Users.idRegion = Regions.idRegion INNER JOIN Sex ON Users.idSex = Sex.idSex WHERE Users.idUser='" + id + "'";

                SqlConnection connection = ConnectionMenager.GetConnection();
                SqlCommand command = new SqlCommand(sql, connection);
                command.CommandType = CommandType.Text;

                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                dataAdapter.Fill(dataSet, "Data");

                connection.Close();
                return dataSet;
            }

                return null;
        }

        /// <summary>
        /// Не работи !
        /// </summary>
        /// <param name="slika"></param>
        /// <param name="idUser"></param>
        public static void AddImage(HttpPostedFile slika,int idUser)
        {
            SqlConnection connection = ConnectionMenager.GetConnection();
           
            byte[] myimage = new byte[slika.ContentLength];

            slika.InputStream.Read(myimage, 0 , (int) slika.ContentLength); // Ovde se puni slikata u InputStream

            SqlCommand storeimageSQL = new SqlCommand(@"UPDATE Users SET slika=@slika WHERE idUser='"+ idUser+ "'", connection);

            storeimageSQL.Parameters.Add("@image", SqlDbType.Image, myimage.Length).Value = myimage;

            storeimageSQL.ExecuteNonQuery();
            connection.Close();
        }
        public static DataTable GetImage(int idUser)
        {
            SqlConnection connection = ConnectionMenager.GetConnection();
            string sql = "Select slika from Users WHERE idUser='"+idUser+"'";
            SqlDataAdapter da = new SqlDataAdapter(sql, connection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }


    }
}